package com.estar.platform.caseInfo.controller;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.model.json.DataGridReturn;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.estar.app.business.common.service.AppService;
import com.estar.app.business.common.utils.XlsxExportUtil;
import com.estar.platform.caseInfo.entity.TmblSurveyReportEntity;

@Controller
@RequestMapping("/surveyReportController")
@Scope("prototype")
public class SurveyReportController extends BaseController {

	@Autowired
	AppService appService;
	@Autowired
	XlsxExportUtil excelUtil; // excel生成工具
	
	private static final Logger logger = Logger.getLogger(SurveyReportController.class);
	
	@RequestMapping(params = "toView")
	public ModelAndView toList(HttpServletRequest request) {
		return new ModelAndView("gdia/report/surveyReport");
	}

	@RequestMapping(params = "datagrid")
	public void datagrid(TmblSurveyReportEntity surveyInfo, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) throws ParseException {
		//甲方车牌号
		String queryCarNo = surveyInfo.getQueryCarsNo();
		//甲方驾驶员
		String queryDriverName = surveyInfo.getQueryJiaDriName();
		//甲方联系方式
		String queryPhone = surveyInfo.getQueryDriverPhone();
		
		//出险次数
		int dangerTimeCount = StringUtils.isEmpty(surveyInfo.getDangerTimeCount())?0:Integer.parseInt(surveyInfo.getDangerTimeCount());
		//地市
		String city = surveyInfo.getCity();
		//区域
		
		//出险时间
		Map<String, String[]> m=request.getParameterMap();
		String[] begin= m.get("dangerTime_begin");
		String[] end=m.get("dangerTime_end");
		//拼装查询语句
		//没输入条件默认查询3个月内3次以上的
		String symbol = " >= ";
		if(dangerTimeCount > 0 && dangerTimeCount < 10) symbol = "=";
		if(dangerTimeCount >= 10) symbol = ">=";
		if(dangerTimeCount == 0) dangerTimeCount = 3;
		StringBuffer sqlTotalCount = new StringBuffer("select count(1) from TMBL_SURVEY_INFO tsi2 left join TMBL_APP_USER tau  on tsi2.t_emp_cde = tau.t_emp_cde where (tsi2.t_cars_no in ( select tsi.t_cars_no from TMBL_SURVEY_INFO tsi group by tsi.t_cars_no having count(1) ").append(symbol).append(dangerTimeCount).append(" ) or tsi2.t_cars_no_yi in ( select ytsi.t_cars_no_yi from TMBL_SURVEY_INFO ytsi group by ytsi.t_cars_no_yi having count(1) ").append(symbol).append(dangerTimeCount).append(" ) ) ");
		StringBuffer sql = new StringBuffer("select tsi2.id as id, tsi2.t_report_no as report_no, tsi2.t_cars_no as car_no1, tsi2.jiadriname as driver_name1, tsi2.t_driver_phone as phone1, tsi2.t_cars_no_yi as car_no2, tsi2.yidriname as driver_name2, tsi2.t_phone_yi as phone2, tau.t_emp_name as emp_name, tsi2.t_work_space as work_space, tsi2.t_traffic_police as traffic_police, tsi2.t_danger_place as danger_place, tsi2.t_case_state as case_state, tsi2.t_check_flag as check_flag, tsi2.t_create_tm as create_tm,tsi2.t_danger_time as danger_time from TMBL_SURVEY_INFO tsi2 left join TMBL_APP_USER tau  on tsi2.t_emp_cde = tau.t_emp_cde where (tsi2.t_cars_no in ( select tsi.t_cars_no from TMBL_SURVEY_INFO tsi group by tsi.t_cars_no having count(1) ").append(symbol).append(dangerTimeCount).append(" )  or tsi2.t_cars_no_yi in ( select ytsi.t_cars_no_yi from TMBL_SURVEY_INFO ytsi group by ytsi.t_cars_no_yi having count(1) ").append(symbol).append(dangerTimeCount).append(" ) ) ");
		 
		if(StringUtils.isNotBlank(queryCarNo)){
			sqlTotalCount.append(" and (tsi2.t_cars_no like '%").append(queryCarNo).append("%' or tsi2.t_cars_no_yi like '%").append(queryCarNo).append("%') ");
			sql.append(" and (tsi2.t_cars_no like '%").append(queryCarNo).append("%' or tsi2.t_cars_no_yi like '%").append(queryCarNo).append("%') ");
		}
		if(StringUtils.isNotBlank(queryDriverName)){
			sqlTotalCount.append(" and (tsi2.jiadriname like '%").append(queryDriverName).append("%' or tsi2.yidriname like '%").append(queryDriverName).append("%') ");
			sql.append(" and (tsi2.jiadriname like '%").append(queryDriverName).append("%' or tsi2.yidriname like '%").append(queryDriverName).append("%') ");
		}
		if(StringUtils.isNotBlank(queryPhone)){
			sqlTotalCount.append(" and (tsi2.t_driver_phone like '%").append(queryPhone).append("%' or tsi2.t_phone_yi like '%").append(queryPhone).append("%') ");
			sql.append(" and (tsi2.t_driver_phone like '%").append(queryPhone).append("%' or tsi2.t_phone_yi like '%").append(queryPhone).append("%') ");
		}
		if(StringUtils.isNotBlank(city)){
			sqlTotalCount.append(" and tsi2.t_city like '%").append(city).append("%' ");
			sql.append(" and tsi2.t_city like '%").append(city).append("%' ");
		}
		String beginDate = "";
		String endDate = "";
		if(null == begin || begin[0].length() <= 0){
			Date nowBeginDate = new Date();   //当前时间			
			beginDate = this.getBeginDate(nowBeginDate, -3);
		}else{
			beginDate = begin[0];
		}
		if(null == end || end[0].length() <= 0){
			endDate = sdf.format(new Date());
		}else{
			endDate = end[0];
		}
		sqlTotalCount.append(" and to_date(tsi2.t_danger_time,'yyyy-mm-dd hh24:mi:ss') between to_date('").append(beginDate).append("','yyyy-mm-dd') and to_date('").append(endDate).append("','yyyy-mm-dd') ");
		sql.append(" and to_date(tsi2.t_danger_time,'yyyy-mm-dd hh24:mi:ss') between to_date('").append(beginDate).append("','yyyy-mm-dd') and to_date('").append(endDate).append("','yyyy-mm-dd') ");
		
		//获取记录总条数
		int totalCount = Integer.parseInt(String.valueOf(appService.getCountForJdbc(sqlTotalCount.toString())));
		//执行查询语句
		List<Map<String, Object>> result = appService.findForJdbc(sql.toString(),dataGrid.getPage(),dataGrid.getRows());
		
		//将查询结果装入实体类中
		List<TmblSurveyReportEntity> list = setEntiry(result);
		
		dataGrid.setResults(list);
		dataGrid.setTotal(totalCount);
		new DataGridReturn(dataGrid.getRows(), list);
		TagUtil.datagrid(response, dataGrid);
	}
	
	/**
	 * 将查询出来的结果装载进实体类中
	 * @param result
	 * @return
	 * @throws ParseException 
	 */
	private List<TmblSurveyReportEntity> setEntiry(List<Map<String, Object>> result) throws ParseException{
		List<TmblSurveyReportEntity> list = new ArrayList<TmblSurveyReportEntity>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		TmblSurveyReportEntity tmblSurveyReportEntity = null;
		for (Map<String, Object> map : result) {
			tmblSurveyReportEntity = new TmblSurveyReportEntity();
			tmblSurveyReportEntity.setId(map.get("ID").toString());
			tmblSurveyReportEntity.setReportNo(map.get("REPORT_NO").toString());
			
			tmblSurveyReportEntity.setCarsNo(map.get("CAR_NO1") == null || "".equals(map.get("CAR_NO1"))?"":map.get("CAR_NO1").toString());
			tmblSurveyReportEntity.setJiaDriName(map.get("DRIVER_NAME1") == null || "".equals(map.get("DRIVER_NAME1"))?"":map.get("DRIVER_NAME1").toString());
			tmblSurveyReportEntity.setDriverPhone(map.get("PHONE1") == null || "".equals(map.get("PHONE1"))?"":map.get("PHONE1").toString());
			
			tmblSurveyReportEntity.setYiCarNo(map.get("CAR_NO2") == null || "".equals(map.get("CAR_NO2"))?"":map.get("CAR_NO2").toString());
			tmblSurveyReportEntity.setYiDriName(map.get("DRIVER_NAME2") == null || "".equals(map.get("DRIVER_NAME2"))?"":map.get("DRIVER_NAME2").toString());
			tmblSurveyReportEntity.setYiPhone(map.get("PHONE2") == null || "".equals(map.get("PHONE2"))?"":map.get("PHONE2").toString());
			
			tmblSurveyReportEntity.setEmpName(map.get("EMP_NAME") == null || "".equals(map.get("EMP_NAME"))?"":map.get("EMP_NAME").toString());
			tmblSurveyReportEntity.setWorkSpace(map.get("WORK_SPACE") == null || "".equals(map.get("WORK_SPACE"))?"":map.get("WORK_SPACE").toString());
			tmblSurveyReportEntity.setTrafficPolice(map.get("TRAFFIC_POLICE") == null || "".equals(map.get("TRAFFIC_POLICE"))?"":map.get("TRAFFIC_POLICE").toString());
			tmblSurveyReportEntity.setDangerPlace(map.get("DANGER_PLACE") == null || "".equals(map.get("DANGER_PLACE"))?"":map.get("DANGER_PLACE").toString());
			tmblSurveyReportEntity.setCaseStatus(map.get("CASE_STATE") == null || "".equals(map.get("CASE_STATE"))?"":map.get("CASE_STATE").toString());
			tmblSurveyReportEntity.setCheckFlag(map.get("CHECK_FLAG") == null || "".equals(map.get("CHECK_FLAG"))?"":map.get("CHECK_FLAG").toString());
			tmblSurveyReportEntity.setCreateTm(map.get("CREATE_TM") == null || "".equals(map.get("CREATE_TM"))?null:new java.sql.Date(sdf.parse(map.get("CREATE_TM").toString()).getTime()));
			tmblSurveyReportEntity.setDangerTime(map.get("DANGER_TIME") == null || "".equals(map.get("DANGER_TIME"))?null:new Timestamp(sdf.parse(map.get("DANGER_TIME").toString()).getTime()));
//			tmblSurveyReportEntity.setDangerTime(map.get("DANGER_TIME") == null || "".equals(map.get("DANGER_TIME"))?null:new java.sql.Date(sdf.parse(map.get("DANGER_TIME").toString()).getTime()));
			list.add(tmblSurveyReportEntity);
		}
		return list;
	}
	
	private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); //设置时间格式
	private String getBeginDate(Date nowBeginDate,int month){
		Calendar calendar = Calendar.getInstance(); //得到日历
		calendar.setTime(nowBeginDate);//把当前时间赋给日历
		calendar.add(calendar.MONTH, month);  //设置为前3月
		nowBeginDate = calendar.getTime();   //得到前3月的时间
		String beginDate = sdf.format(nowBeginDate);    //格式化前3月的时间
		return beginDate;
	}
}
